﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Backend;
using System.Threading.Tasks;

namespace Data_Layer
{
    public class DBBooking : IDBBooking
    {
        SqlConnection con;
        List<Booking> allBookings = new List<Booking>();

        public DBBooking()
        {
            con = Connection.GetInstance().GetConnection();
        }

        public bool createBooking(Booking booking)
        {
            try
            {
                con.Open();
                String Query = "use Internship insert into Booking values(" + booking.BookingNO + ",'" + booking.PlateNO + "', '" + booking.Status + "', " + booking.TimeBooked_hours + ",'" + booking.StartTime + "'" + ")";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();

                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with createBooking()");
                Console.WriteLine(ex.Message);

                return false;

            }

            finally
            {
                con.Close();
            }

        }

        public bool deleteBooking(int bookingNO)
        {
            try
            {
                con.Open();
                String Query = "use Internship delete from Booking where bookingNO = " + bookingNO;
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();

                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with deleteBooking()");
                Console.WriteLine(ex.Message);

                return false;

            }

            finally
            {
                con.Close();
            }

        }

        public Booking readBooking(int bookingNO)
        {
             try
            {
                con.Open();
                Booking booking = new Booking();
                String Query = "use Internship select * from Booking where bookingNO =" + bookingNO;
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    booking.BookingNO = (int)reader["bookingNO"];
                    booking.PlateNO = (string)reader["plateNO"];
                    booking.Status = (string)reader["status_"];
                    booking.TimeBooked_hours = (int)reader["bookedTime"];
                    booking.StartTime = Convert.ToString(reader["startTime"]);
                    
                }
                
                reader.Close();
                return booking;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with readBooking()");
                Console.WriteLine(ex.Message);
                
                return null;

            }

            finally
            {
                con.Close();
            }
        }

        public bool updateBooking(Booking booking)
        {
            try
            {
                con.Open();
                String Query = "use Internship update Booking set " + "plateNO=" + "'" + booking.PlateNO + "', " + "status_=" + "'" + booking.Status + "', " + "bookedTime=" + booking.TimeBooked_hours + ", " + "startTime=" + "'" + booking.StartTime + "'" + " where bookingNO = " + booking.BookingNO;
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                reader.Close();

                return true;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with updateBooking()");
                Console.WriteLine(ex.Message);

                return false;

            }

            finally
            {
                con.Close();
            }
        }

        public List<Booking> getallBookings()
        {
            try
            {                
                con.Open();
                String Query = "use Internship select * from Booking";
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                        Booking booking = new Booking();
                        booking.BookingNO = (int)reader["bookingNO"];
                        booking.PlateNO = (string)reader["plateNO"];
                        booking.Status = (string)reader["status_"];
                        booking.TimeBooked_hours = (int)reader["bookedTime"];
                        booking.StartTime = Convert.ToString(reader["startTime"]);
                        allBookings.Add(booking);                    
                }

                reader.Close();

                return allBookings;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with getallBookings()");
                Console.WriteLine(ex.Message);

                return allBookings;
            }

            finally
            {
                con.Close();
            }
        }

        public List<Booking> getallBookingsbybookingNO(int bookingNO)
        {
            try
            {
                con.Open();
                String Query = "use Internship select * from Booking where bookingNO=" + bookingNO;
                System.Console.WriteLine(Query);
                System.Diagnostics.Debug.WriteLine("Query: " + Query);
                SqlCommand myCommand = new SqlCommand(Query, con);
                SqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    Booking booking = new Booking();
                    booking.BookingNO = (int)reader["bookingNO"];
                    booking.PlateNO = (string)reader["plateNO"];
                    booking.Status = (string)reader["status_"];
                    booking.TimeBooked_hours = (int)reader["bookedTime"];
                    booking.StartTime = Convert.ToString(reader["startTime"]);
                    allBookings.Add(booking);
                }

                reader.Close();

                return allBookings;
            }

            catch (Exception ex)
            {
                Console.WriteLine("Error: Something went wrong with getallBookingsbybookingNO()");
                Console.WriteLine(ex.Message);

                return allBookings;
            }

            finally
            {
                con.Close();
            }
        }
    }
}
